-------------PROC--------------


				---------------Account---------------
CREATE PROC sp_Account_Insert
	@Account varchar(500),
	@Password varchar(100),
	@Name varchar(500),
	@Role int,
	@Status int
AS
INSERT INTO Account ([Username],[Password],[Name],[Role],[Status]) VALUES (@Account,@Password,@Name,@Role,@Status)
GO

CREATE PROC sp_Account__Update_Pass
	@Username varchar(500),
	@Password varchar(100)
AS
UPDATE Account SET [Password]=@Password WHERE [Username]=@Username
GO

CREATE PROC sp_Account_Update_Info
	@Username varchar(500),
	@Name varchar(500),
	@Role int,
	@Status int
AS
UPDATE Account SET [Name]=@Name , [Role]= @Role, [Status]=@Status WHERE [Username]=@Username
GO

CREATE PROC sp_Account_GetByAll
AS
SELECT * FROM Account
GO

CREATE PROC sp_Account_CheckLogin
	@Username varchar(500),
	@Password varchar(100)
AS
SELECT * FROM Account WHERE [Username]=@Username AND [Password]=@Password
GO



				---------------Contact---------------
CREATE PROC sp_Contact_Insert
	@Name varchar(500),
	@Address varchar(500),
	@Phone varchar(20),
	@Email varchar(500),
	@Detail varchar(max),
	@Date date
AS
INSERT INTO Contact ([Name],[Address],[Phone],[Email],[Detail],[Date]) VALUES (@Name ,@Address,@Phone,@Email,@Detail,@Date)
GO

CREATE PROC sp_Contact_GetByAll
AS
SELECT * FROM Contact
GO

				---------------Passenger---------------
CREATE PROC sp_Passenger_Insert
	@Username varchar(100),
	@Password varchar(100),
	@Name varchar(500),
	@Bith date,
	@Address varchar(500),
	@Phone varchar(20),
	@Email varchar(500)
AS
INSERT INTO Passenger([Username],[Password],[Name],[Birth],[Address],[Phone],[Email])
VALUES (@Username,@Password,@Name,@Bith,@Address,@Phone,@Email)
GO

CREATE PROC sp_Passenger_GetAll
AS
SELECT * FROM Passenger
GO

CREATE PROC sp_Passenger_Update_Pass
	@Username varchar(100),
	@Password varchar(100)
AS
UPDATE Passenger SET [Password]=@Password WHERE [Username]=@Username
GO

CREATE PROC sp_Passenger_Update_Info
	@Username varchar(500),
	@Name varchar(500),
	@Address varchar(500),
	@Phone varchar(20),
	@Email varchar(500)
AS
UPDATE Passenger SET [Name]=@Name,[Address]=@Address,[Phone]=@Phone,[Email]=@Phone WHERE [Username]=@Username
GO

CREATE PROC sp_Passenger_CheckLogin
	@Username varchar(100),
	@Password varchar(100)
AS
SELECT Username,Password FROM Passenger WHERE Username = @Username AND [Password]=@Password
GO 

CREATE PROC sp_Passenger_Delete
	@Username varchar(500)
AS
DELETE FROM Passenger WHERE [Username] = @Username 
GO

CREATE PROC sp_Passenger_GetByName
	@Username varchar(500)
AS
SELECT * FROM Passenger WHERE [Username] = @Username;
GO


				---------------Route---------------
CREATE PROC sp_Route_Insert
	@Route_Nane varchar(500),
	@Depature varchar(500),
	@Destination varchar(500),
	@Distance int
AS
INSERT INTO [Route] ([Route_Name],[Depature],[Destination],[Distance]) VALUES (@Route_Nane,@Depature,@Destination,@Distance)
GO

CREATE PROC sp_Route_Update
	@Route_Nane varchar(500),
	@Depature varchar(500),
	@Destination varchar(500),
	@Distance int
AS
UPDATE [Route] SET Depature=@Depature , Destination=@Destination, Distance=@Distance WHERE Route_Name=@Route_Nane
GO

CREATE PROC sp_Route_GetAll
AS
SELECT * FROM [Route]
GO


				---------------Train---------------
CREATE PROC sp_Train_Insert
	@Train_Name varchar(500),
	@Station_Name varchar(500),
	@Route_Name varchar(500),
	@AC_1 int,
	@AC_2 int,
	@AC_3 int,
	@Sleeper int,
	@General int
AS
INSERT INTO Train ([Train_Name],[Station_Name],[Route_Name],[AC_1],[Ac_2],[Ac_3],[Sleeper],[General]) VALUES 
(@Train_Name,@Station_Name,@Route_Name,@AC_1,@AC_2,@AC_3,@Sleeper,@General)
GO

CREATE PROC sp_Train_Update_ByID
	@Train_No int,
	@Train_Name varchar(500),
	@Station_Name varchar(500),
	@Route_Name varchar(500),
	@AC_1 int,
	@AC_2 int,
	@AC_3 int,
	@Sleeper int,
	@General int
AS
UPDATE Train SET [Train_Name]=@Train_Name , [Station_Name] = @Station_Name, [Route_Name]=@Route_Name , [AC_1]=@AC_1,
[Ac_2]=@AC_2, [Ac_3]=@AC_3, [Sleeper]=@Sleeper, [General]=@General WHERE [Train_No]=@Train_No
GO

CREATE PROC sp_Train_Delete
	@Train_No int 
AS
DELETE FROM Train WHERE [Train_No]=@Train_No
GO

CREATE PROC sp_Train_GetAll
AS
SELECT * FROM Train
GO


				---------------Station---------------

CREATE PROC sp_Station_Insert
	@Station_Name varchar(500),
	@Address varchar(500),
	@Phone varchar(20)
AS
INSERT INTO Station ([Station_Name] , [Address], [Phone]) VALUES (@Station_Name,@Address,@Phone)
GO

CREATE PROC sp_Station_Update
	@Station_Name varchar(500),
	@Address varchar(500),
	@Phone varchar(20)
AS
UPDATE Station SET [Address] = @Address , [Phone] =@Phone WHERE [Station_Name] = @Station_Name
GO

CREATE PROC sp_Station_GetByAll
AS
SELECT * FROM Station
GO


				---------------Schedule---------------
CREATE PROC sp_Schedule_Insert
	@Date date,
	@Time time
AS
INSERT INTO Schedule ([Date], [Time]) VALUES (@Date, @Time)
GO

CREATE PROC sp_Schedule_GetByAll
AS
SELECT * FROM Schedule
GO


				---------------Train_Schedule---------------
CREATE PROC sp_Train_Schedule_Insert
	@Schedule_ID int,
	@Train_No int
AS
INSERT INTO Train_Schedule ([Schedule_ID],[Train_No]) VALUES (@Schedule_ID,@Train_No)
GO

CREATE PROC sp_Train_Schedule_GetAll
AS
SELECT Train_Name,Station_Name,Route_Name FROM Train,Schedule 
GO

				---------------Order---------------

CREATE PROC sp_Order_Insert
	@Username varchar(100),
	@Total float,
	@Date date
AS
INSERT INTO [Order] ([Username],[Total],[Date]) VALUES (@Username,@Total,@Date)
GO


				---------------Order Detail---------------
CREATE PROC sp_OrderDetail_Insert
	@Ord_ID int,
	@Train_Schedule_ID int,
	@Type_Ticket varchar(50),
	@Quantity int
AS
INSERT INTO OrderDetail ([Ord_ID],[Train_Schedule_ID],[Type_Ticket],[Quantity]) VALUES
(@Ord_ID,@Train_Schedule_ID,@Type_Ticket,@Quantity)
GO

CREATE PROC sp_OrderDetail_GetAll
AS
SELECT ID, Username, [Date] ,Type_Ticket, Quantity FROM OrderDetail, [Order] WHERE OrderDetail.Ord_ID = [Order].Ord_ID
GO